﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.DataAccessProvider;
using System.Data.SqlClient;
using DTN.Core;
using System.Data;
using DTN.Core.Common;
using DTN.Core.Utilities;

namespace com.dtn.deals
{
    public class tbl_CategoryManager : Itbl_Categorymanager
    {
        private static Itbl_Categorymanager itbl_CategoryManager = null;
        private tbl_CategoryManager()
        {
        }
        public static Itbl_Categorymanager CreateInstance()
        {
            if (itbl_CategoryManager == null)
            {
                itbl_CategoryManager = new tbl_CategoryManager();
            }
            return itbl_CategoryManager;
        }

        public int inserttbl_Category(tbl_Category cate) 
        {
            DataProviderBaseClass dataProvider = DataProvider.GetDataProvider(EnumDataProviderType.SqlClient, DataHelper.GetConnectionString());
            int inumOfRowEffect = 0;
            SqlParameter[] parameters = new SqlParameter[]{
																new SqlParameter("@ID", SqlDbType.Int),
																new SqlParameter("@cateName", SqlDbType.NVarChar),
																new SqlParameter("@cateParrent", SqlDbType.Int),
                                                                new SqlParameter("@image", SqlDbType.NVarChar),
                                                                new SqlParameter("@isHome", SqlDbType.Int),
                                                                new SqlParameter("@Order", SqlDbType.Int),
																new SqlParameter("@cDate", SqlDbType.DateTime),
                                                                new SqlParameter("@GroupFilterId", SqlDbType.Int),
                                                                new SqlParameter("@FilterId", SqlDbType.NVarChar),
															};
            parameters[0].Direction = ParameterDirection.Output;
            parameters[1].Value = cate.CateName;
            parameters[2].Value = cate.CateParrent;
            parameters[3].Value = cate.Images;
            parameters[4].Value = cate.IsHome ;
            parameters[5].Value = cate.Order;
            parameters[6].Value = cate.CDate;
            parameters[7].Value = cate.GroupFilterId;
            parameters[8].Value = cate.FilterId;

            try
            {
                inumOfRowEffect = dataProvider.ExecuteNonQuery("sp_Inserttbl_Category", CommandType.StoredProcedure, parameters);
                cate.ID = Convert.ToInt32(parameters[0].Value);
            }
            catch (Exception ex)
            {
                cate.ID = -1;
            }
            return cate.ID;
        }

        public bool updatetbl_Category(tbl_Category cate) 
        {
            DataProviderBaseClass dataProvider = DataProvider.GetDataProvider(EnumDataProviderType.SqlClient, DataHelper.GetConnectionString());
            bool ret = true;
            SqlParameter[] parameters = new SqlParameter[]{
																new SqlParameter("@ID", SqlDbType.Int),
																new SqlParameter("@cateName", SqlDbType.NVarChar),
																new SqlParameter("@cateParrent", SqlDbType.Int),
                                                                new SqlParameter("@image", SqlDbType.NVarChar),
                                                                new SqlParameter("@isHome", SqlDbType.Int),
                                                                new SqlParameter("@Order", SqlDbType.Int),
																new SqlParameter("@cDate", SqlDbType.DateTime),
                                                                new SqlParameter("@GroupFilterId", SqlDbType.Int),
                                                                new SqlParameter("@FilterId", SqlDbType.NVarChar),
															};
            parameters[0].Value = cate.ID;
            parameters[1].Value = cate.CateName;
            parameters[2].Value = cate.CateParrent;
            parameters[3].Value = cate.Images;
            parameters[4].Value = cate.IsHome;
            parameters[5].Value = cate.Order;
            parameters[6].Value = cate.CDate;
            parameters[7].Value = cate.GroupFilterId;
            parameters[8].Value = cate.FilterId;
            try
            {
                dataProvider.ExecuteNonQuery("sp_Updatetbl_Category", CommandType.StoredProcedure, parameters);
            }
            catch (Exception ex)
            {
                ret = false;
            }
            return ret;
        }

        public bool Deletetbl_category(int cateID) 
        {
            DataProviderBaseClass dataProvider = DataProvider.GetDataProvider(EnumDataProviderType.SqlClient, DataHelper.GetConnectionString());
            bool ret = true;
            SqlParameter[] parameters = new SqlParameter[]{
																new SqlParameter("@ID", SqlDbType.Int),
															};
            parameters[0].Value = cateID;
            
            try
            {
                dataProvider.ExecuteNonQuery("sp_Deletetbl_Category", CommandType.StoredProcedure, parameters);
            }
            catch (Exception ex)
            {
                ret = false;
            }
            return ret;
        }

        public DataTable Selecttbl_CategoryWithPageDynamic(int StartIndex, int MaxRecords, string Where) 
        {
            DataSet ds = new DataSet();
            DataProviderBaseClass dataProvider = DataProvider.GetDataProvider(EnumDataProviderType.SqlClient, DataHelper.GetConnectionString());
            SqlParameter[] parrams = { new SqlParameter("@Startindex",SqlDbType.Int ){Value = StartIndex },
                                         new SqlParameter("@Maxrecords",SqlDbType.Int ){Value = MaxRecords  },
                                         new SqlParameter("@WhereCondition",SqlDbType.NVarChar,500 ){Value = Where  }
                                     };
            try
            {
                ds = dataProvider.ExecuteDataSet("sp_Selecttbl_CategoryWithPagesDynamic", CommandType.StoredProcedure, parrams);
            }
            catch (Exception ex)
            {
                return null;
            }
            return ds.Tables["Table"];
        }

        public DataTable Selecttbl_CategoryDynamic(string Where, string order) 
        {
            DataSet ds = new DataSet();
            DataProviderBaseClass dataProvider = DataProvider.GetDataProvider(EnumDataProviderType.SqlClient, DataHelper.GetConnectionString());
            SqlParameter[] parameters = new SqlParameter[]{
																new SqlParameter("@WhereCondition", SqlDbType.NVarChar,500),
																new SqlParameter("@OrderByExpression", SqlDbType.NVarChar,250),
															};
            parameters[0].Value = Where;
            parameters[1].Value = order;
            try
            {
                ds = dataProvider.ExecuteDataSet("sp_Selecttbl_CategoryDynamic", CommandType.StoredProcedure, parameters);
            }
            catch (Exception ex)
            {
                return null;
            }
            return ds.Tables["Table"];
        }
        //Cuong Code
        public static DataTable GetAll()
        {
            DataTable retVal = null;
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("sp_Selecttbl_CategoryAll", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            try
            {
                retVal = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        public static DataTable GetAllParent()
        {
            DataTable retVal = null;
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("sp_Selecttbl_CategoryAllParent", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            try
            {
                retVal = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        public static DataTable GetAllByParentId(int parentid, int top)
        {
            DataTable retVal = null;
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("sp_Selecttbl_CategoryAllByParentId", dbConn);
            dbCmd.Parameters.AddWithValue("@ParentId", parentid);
            dbCmd.Parameters.AddWithValue("@top", top);
            dbCmd.CommandType = CommandType.StoredProcedure;
            try
            {
                retVal = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public static DataTable GetAndPagingChildCateByParent(int parentId, int _pageSize,
                                                  int _currentPage, out int _totalRecord)
        {
            DataTable retVal = null;
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);

            SqlCommand dbCmd = new SqlCommand("tbl_Category_GetAndPagingChildCateByParent", dbConn);
            dbCmd.Parameters.AddWithValue("@PageSize", _pageSize);
            dbCmd.Parameters.AddWithValue("@PageIndex", _currentPage);
            dbCmd.Parameters.AddWithValue("@ParentId", parentId);
            dbCmd.Parameters.Add(new SqlParameter("@Count", SqlDbType.Int));
            dbCmd.Parameters["@Count"].Direction = ParameterDirection.Output;
            dbCmd.CommandType = CommandType.StoredProcedure;
            try
            {
                retVal = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);

                _totalRecord = ConvertUtility.ToInt32(dbCmd.Parameters["@Count"].Value);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        public static tbl_Category GetInfo(int iD)
        {
            tbl_Category retVal = null;
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("tbl_Category_GetInfo", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.Add("@ID", iD);
            try
            {
                dbConn.Open();
                SqlDataReader dr = dbCmd.ExecuteReader();
                if (dr.Read()) retVal = new tbl_Category(dr);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }


        public DataTable SelectAllSubCateWithPageDynamic(int index, int PageSize, string where)
        {
            DataSet ds = new DataSet();
            DataProviderBaseClass dataProvider = DataProvider.GetDataProvider(EnumDataProviderType.SqlClient, DataHelper.GetConnectionString());
            SqlParameter[] parrams = { new SqlParameter("@Startindex",SqlDbType.Int ){Value = index },
                                         new SqlParameter("@Maxrecords",SqlDbType.Int ){Value = PageSize  },
                                         new SqlParameter("@WhereCondition",SqlDbType.NVarChar,500 ){Value = where  }
                                     };
            try
            {
                ds = dataProvider.ExecuteDataSet("sp_SelectAllSubCateWithPageDynamic", CommandType.StoredProcedure, parrams);
            }
            catch (Exception ex)
            {
                return null;
            }
            return ds.Tables["Table"];
        }


        public tbl_Category SelectCateById(int id)
        {
            IDataReader dr;
            tbl_Category tbl_Cate = new tbl_Category();
            DataProviderBaseClass dataProvider = DataProvider.GetDataProvider(EnumDataProviderType.SqlClient, DataHelper.GetConnectionString());
            SqlParameter[] parameters = new SqlParameter[]{
																new SqlParameter("@ID", SqlDbType.Int),
															};
            parameters[0].Value = id;
            try
            {
                dr = dataProvider.ExecuteReader("sp_SelectCateByID", CommandType.StoredProcedure, parameters);
                while (dr.Read())
                {
                    tbl_Cate.ID = Convert.ToInt32(dr["ID"].ToString());
                    tbl_Cate.CateName = Convert.ToString(dr["cateName"].ToString());
                    tbl_Cate.CateParrent = Convert.ToInt32(dr["cateParrent"].ToString());
                    tbl_Cate.Images = Convert.ToString(dr["images"].ToString());
                    tbl_Cate.IsHome = Convert.ToInt32(dr["IsHome"].ToString());
                    tbl_Cate.Order = Convert.ToInt32(dr["Order"].ToString());
                    tbl_Cate.CDate = Convert.ToDateTime(dr["cDate"].ToString());
                    tbl_Cate.GroupFilterId = Convert.ToInt32(dr["GroupFilterId"].ToString());
                    tbl_Cate.FilterId = Convert.ToString(dr["FilterId"].ToString());
                }
            }
            catch (Exception ex)
            {
                return null;
            }
            return tbl_Cate;
        }


        public bool IsExistedSubCateByCateID(int cateId)
        {
            SqlConnection dbConn = new SqlConnection(DataHelper.GetConnectionString());
            try
            {

                dbConn.Open();
                SqlCommand dbCmd = new SqlCommand("sp_IsExistedSubCateByCateID", dbConn);
                dbCmd.Parameters.AddWithValue("@ID", cateId);
                dbCmd.Parameters.Add("@RETURN_VALUE", SqlDbType.Int);
                dbCmd.Parameters[1].Direction = ParameterDirection.ReturnValue;
                dbCmd.CommandType = CommandType.StoredProcedure;

                int kq = dbCmd.ExecuteNonQuery();
                int totalrow = int.Parse(dbCmd.Parameters["@RETURN_VALUE"].Value.ToString());
                if (totalrow > 0)
                    return true;

            }
            catch (Exception ex)
            {
                return false;
            }
            finally
            {
                dbConn.Close();
            }
            return false;
        }


        public DataTable SelectAllSubCateByCateIDAndPaging(int index, int pageSize, string Where)
        {
            DataSet ds = new DataSet();
            DataProviderBaseClass dataProvider = DataProvider.GetDataProvider(EnumDataProviderType.SqlClient, DataHelper.GetConnectionString());
            SqlParameter[] parrams = { new SqlParameter("@Startindex",SqlDbType.Int ){Value = index },
                                         new SqlParameter("@Maxrecords",SqlDbType.Int ){Value = pageSize  },
                                         new SqlParameter("@WhereCondition",SqlDbType.NVarChar,500 ){Value = Where  }
                                     };
            try
            {
                ds = dataProvider.ExecuteDataSet("sp_SelectAllSubCateByCateId", CommandType.StoredProcedure, parrams);
            }
            catch (Exception ex)
            {
                return null;
            }
            return ds.Tables["Table"];
        }


        public DataTable countTimeandDealByCategory(int catId)
        {
            DataSet ds = new DataSet();
            DataProviderBaseClass dataProvider = DataProvider.GetDataProvider(EnumDataProviderType.SqlClient, DataHelper.GetConnectionString());
            SqlParameter[] parrams = { new SqlParameter("@CatID",SqlDbType.Int ){Value = catId }
                                     };                           
            try
            {
                ds = dataProvider.ExecuteDataSet("sp_SelectCountTimeanddealbyCate", CommandType.StoredProcedure, parrams);
            }
            catch (Exception ex)
            {
                return null;
            }
            return ds.Tables["Table"];
        }
    }
}
